package com.koron.css2.serviceManage.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CreateOracleUserMapper {

	/**
	 * 创建数据表空间
	 * @param name
	 * @return
	 */
	@Select("create tablespace ${name} DATAFILE 'D:/app/Administrator/oradata/CSS/${name}.dbf' "
			+ "size 100m autoextend on next 100m extent management local")
	void createDataTablespace(@Param("name") String name);

	/**
	 * 创建索引表空间
	 * @param name
	 * @return
	 */
	@Select("create tablespace ${name} DATAFILE 'D:/app/Administrator/oradata/CSS/${name}.dbf' "
			+ "size 100m autoextend on next 100m extent management local")
	void createIndexTablespace(@Param("name") String name);

	/**
	 * 创建临时表空间
	 * @param name
	 * @return
	 */
	@Select("create temporary tablespace ${name} tempfile 'D:/app/Administrator/oradata/CSS/${name}.dbf' "
			+ "size 100m autoextend on next 32m extent management local")
	void createTempTablespace(@Param("name") String name);

	/**
	 * 创建用户
	 * @param schema
	 * @param password
	 * @param dataTableSpace
	 * @param tempTableSpace
	 */

	@Select("create user ${schema} identified by ${password} default tablespace ${dataTableSpace} temporary tablespace ${tempTableSpace}")
	void createUser(@Param("schema") String schema,@Param("password") String password,
						@Param("dataTableSpace") String dataTableSpace,@Param("tempTableSpace") String tempTableSpace);

	/**
	 * 授予权限
	 * @param schema
	 * @return
	 */
	@Select("grant connect,resource,DROP ANY TABLE,create view to ${schema}")
	void grantUser(@Param("schema") String schema);

	/**
	 * 获取用户所有表名
	 * @param schema
	 * @return
	 */
	@Select("select table_name from all_tables where owner = #{schema}")
	List<String> getUserAllTables(@Param("schema") String schema);

	/**
	 * 授予跨用户查询权限
	 * @param schema
	 */
	@Select("grant select any table to ${schema}")
	void grantSelectAny(@Param("schema") String schema);

	/**
	 * 撤销跨用户查询权限
	 * @param schema
	 */
	@Select("revoke select any table from ${schema}")
	void revokeSelectAny(@Param("schema") String schema);
	
	/**
	 * 复制表结构
	 * @param schema
	 * @param name
	 * @return
	 */
	@Select("create table ${schema}.${name} as select * from ${templateSchema}.${name} where 1 = 2")
	Integer copyTable(@Param("templateSchema") String templateSchema, @Param("schema") String schema, @Param("name") String name);

	/**
	 * 获取模板表名
	 * @param schema
	 * @return
	 */
	@Select("select table_name from gdh_template_config")
	List<String> getTemplateTables();

	/**
	 * 复制模板数据
	 * @param schema
	 * @param name
	 */
	@Select("insert into ${schema}.${name} select * from ${templateSchema}.${name}")
	void copyTemplateData(@Param("templateSchema") String templateSchema, @Param("schema") String schema, @Param("name") String name);
}
